﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Globalization;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Runtime.InteropServices;
using System.Security.Cryptography.X509Certificates;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using wne.Context;
using wne.core.Interface;
using wne.models;
using wne.models.Filter;

namespace wne.core
{
    public class AdminFacadeSql :AdminFacade
    {
        public override Dictionary<int, string> GetAllGroups()
        {
            using (var context = new tpksEntities())
            {
                return context.Group.ToDictionary(x => x.Group_ID, x => x.Group_Name);
            }
        }


        static void Fieldtodbfield(Group_Field dbfield,FieldModel field)
        {
            // здесь был я
            dbfield.Field_Name = field.Name;
            dbfield.Field_Type = (int)field.Type;
            /*if (dbfield.Field_Type == 4)
                dbfield.Field_Value = field.SelectedValues[0].ToString();
            else
            if (dbfield.Field_Type == 5)
            {
                dbfield.Field_Value = "";
                for (int i = 0; i < field.SelectedValues.Count; i++)
                {
                    if (i != 0)
                        dbfield.Field_Value = dbfield.Field_Value + ",";
                    dbfield.Field_Value = dbfield.Field_Value+field.SelectedValues[i].ToString();   
                }
            }
            else
            {
                dbfield.Field_Value = field.Value;
            }*/
            dbfield.Field_Value = "";
            for (int i = 0; i < field.AvaliableValues.Count; i++)
            {
                if (i != 0)
                    dbfield.Field_Value = dbfield.Field_Value + ",";
                dbfield.Field_Value = dbfield.Field_Value + field.AvaliableValues[i];
            }
            if (!field.Mandatory)
                dbfield.Mandatory_Flag = "0";
            else
                dbfield.Mandatory_Flag = "1";
            if (!field.UserCanEdit)
                dbfield.User_Edit_Flag = "0";
            else
                dbfield.User_Edit_Flag = "1";

        }
        void dbfieldtofield(FieldModel field, Group_Field dbfield)
        {
            field.Name = dbfield.Field_Name;
            field.Type = (FieldTypeEnum)dbfield.Field_Type;
            field.FieldId = dbfield.Field_ID;
            if (dbfield.Mandatory_Flag == "0")
                field.Mandatory = false;
            else
                field.Mandatory = true;
            if (dbfield.User_Edit_Flag == "0")
                field.UserCanEdit = false;
            else
                field.UserCanEdit = true;
            if ((dbfield.Field_Type == 4) || (dbfield.Field_Type == 5))
            {
                String[] ms = dbfield.Field_Value.Split(',');
                for (int i = 0; i < ms.Count(); i++)
                {
                    field.AvaliableValues.Add(i, ms[i]);
                }
            }

        }
        void usertodbuser(User dbuser, UserModel user)
        {
            dbuser.Birth_Date = DateTime.ParseExact(user.UserBd,"dd.MM.yyyy", CultureInfo.InvariantCulture);
            dbuser.First_Name = user.FirstName;
            dbuser.ICQ = int.Parse(user.Phone);
            dbuser.Last_Name = user.Surname;
            dbuser.Second_Name = user.SecondName;
            dbuser.Login = user.UserName;
            dbuser.Profession = user.UserName;
            dbuser.First_Name = user.FirstName;
            dbuser.Email = user.UserEmail;
        }

        void dbusertouser(UserModel user, User dbuser)
        {
            user.UserBd = dbuser.Birth_Date.ToString("dd.MM.yyyy");
            user.FirstName=dbuser.First_Name;
            user.Phone=dbuser.ICQ.ToString();
            user.Surname=dbuser.Last_Name;
            user.SecondName=dbuser.Second_Name;
            //user.UserName=dbuser.Profession;
            user.UserName=dbuser.Login;
            user.FirstName=dbuser.First_Name;
            user.Id = dbuser.User_ID;
            user.UserEmail = dbuser.Email;
        }
        #region Groups




        public GroupSmallModel GetGroupSmallById(int pId)
        {
            try
            {
                models.GroupSmallModel tmpgroup = new models.GroupSmallModel();
                using (var context = new tpksEntities())
                {
                    var rd = context.Group.FirstOrDefault(x => x.Group_ID == pId);
                    tmpgroup.Id = rd.Group_ID;
                    tmpgroup.Name = rd.Group_Name;
                    tmpgroup.Count = rd.User.Count;

                    List<models.FieldModel> fieldlist = new List<models.FieldModel>();
                    //var Query = (from gr in context.Group_Field where gr.Group_ID == pId select gr).ToList();
                    var Query = rd.Group_Field.ToList();
                    foreach (var item in Query)
                    {
                        models.FieldModel fieldModel = new models.FieldModel();
                        fieldModel.Name = item.Field_Name;
                        fieldModel.Value = item.Field_Value;
                        models.FieldTypeEnum type = new models.FieldTypeEnum();
                        type = (models.FieldTypeEnum)item.Field_Type;
                        fieldModel.Type = type;
                        if ((item.User_Edit_Flag)[0] == '1')
                            fieldModel.UserCanEdit = true;
                        else
                            fieldModel.UserCanEdit = false;
                        if ((item.Mandatory_Flag)[0] == '1')
                            fieldModel.Mandatory = true;
                        else
                            fieldModel.Mandatory = false;
                        fieldlist.Add(fieldModel);
                    }
                    tmpgroup.Fields = fieldlist;
                    int count = 0;
                    int max_count = 0;
                    var query = (from grr in context.Group_Relation select grr).ToList();
                    int level = FindMaxLevel(pId, context, ref count, ref max_count, query);

                    tmpgroup.Level = level;

                    return tmpgroup;
                }

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.ToString());
                return null;
            }
        }

        /*
        private static int FindMaxLevel(int id, tpksEntities context, ref int count, ref int max_count)
        {
            var query = from grr in context.Group_Relation where grr.Child_Group_ID == id select grr;
            foreach (var item in query)
            {
                count++;
                FindMaxLevel(item.Parent_Group_ID, context, ref count, ref max_count);
            }
            if (count > max_count)
            {
                max_count = count;
            }
            count--;

            //list.Add(count);
            return max_count;
        }
           
        */
        private static int FindMaxLevel(int id, tpksEntities context, ref int count, ref int max_count, System.Collections.Generic.List<Group_Relation> query)
        {
            //var query = from grr in context.Group_Relation where grr.Child_Group_ID == id select grr;
            var query1 = query.Where(s => s.Child_Group_ID == id);
            foreach (var item in query1)
            {
                count++;
                FindMaxLevel(item.Parent_Group_ID, context, ref count, ref max_count, query);
            }
            if (count > max_count)
            {
                max_count = count;
            }
            count--;

            //list.Add(count);
            return max_count;
        }



        /*
          List<GroupModel> GetAllGroups(int OrderNum);
            OrderNum=1 - Сортировка по алфавиту по умолчанию
            2-по алфавиту в обратном порядке
            3-по возрастанию количества юзеров
            4-по убыванию количества юзеров
         */
        public static int qmagic1(List<models.GroupSmallModel> tmplist, List<models.GroupSmallModel> ourlist, int par, System.Collections.Generic.List<Group_Relation> query)
        {
            AdminFacadeSql a = new AdminFacadeSql();
            int tec = 0;
            int child = par;
            while (tec < tmplist.Count)
            {
                //foreach (var kvp in a.GetGroupById(tmplist[tec].Id).SelectedParents)
                foreach (var kvp in query.FindAll(x => x.Child_Group_ID == tmplist[tec].Id))
                {
                    //if (kvp.Key == ourlist[par].Id)
                    if (kvp.Parent_Group_ID == ourlist[par].Id)
                    {
                        var tmp = new models.GroupSmallModel();
                        tmp.Id = tmplist[tec].Id;
                        tmp.Level = tmplist[tec].Level;
                        tmp.Count = tmplist[tec].Count;
                        tmp.Name = tmplist[tec].Name;
                        tmp.Fields = tmplist[tec].Fields;
                        ourlist.Insert(child + 1, tmp);
                        ourlist[child + 1].Level = ourlist[par].Level + 1;
                        child = qmagic1(tmplist, ourlist, child + 1, query);
                    }
                }
                tec++;
            }
            return child;
        }
        /*
        public override List<GroupSmallModel> GetFilteredGroupSmall(GroupSmallFilter pFilter)
        {
            int OrderNum;
            if ((pFilter.OrderEnum == (GroupSmallOrderEnum)1) && (pFilter.IsOrderDesc == true))
                OrderNum = 1;
            else
            {
                if ((pFilter.OrderEnum == (GroupSmallOrderEnum)1) && (pFilter.IsOrderDesc == false))
                    OrderNum = 2;
                else
                {
                    if ((pFilter.OrderEnum == (GroupSmallOrderEnum)2) && (pFilter.IsOrderDesc == true))
                        OrderNum = 3;
                    else
                    {
                        if ((pFilter.OrderEnum == (GroupSmallOrderEnum)2) && (pFilter.IsOrderDesc == false))
                            OrderNum = 4;
                        else
                        {
                            OrderNum = 0;
                        }
                    }
                }
            }

            List<models.GroupSmallModel> tmplist = GetAllGroupsss(OrderNum);
            if (tmplist.Count == 0) return tmplist;
            List<models.GroupSmallModel> ourlist = GetAllGroupsss(OrderNum);
            int qq = 0;
            int lvl = 0;
            while (qq == 0)
            {
                for (int i = 0; i < ourlist.Count; i++)
                {
                    if (ourlist[i].Level == lvl)
                    {
                        models.GroupSmallModel temp = ourlist[i];
                        ourlist.Remove(ourlist[i]);
                        ourlist.Insert(qq, temp);
                        qq++;
                    }
                }
                lvl++;
            }
            using (var context = new tpksEntities())
            {
                int iii = 0;
                var query = (from qwe in context.Group_Relation select qwe).ToList();
                while (qq > 0)
                {
                    iii = qmagic1(tmplist, ourlist, iii, query) + 1;
                    qq--;
                }
                //iii++;
                ourlist.RemoveRange(iii, ourlist.Count - iii);
            }
            return ourlist;
        }
        */
        public List<GroupSmallModel> ChildDetector(GroupSmallModel papa, List<GroupSmallModel> tmplist, System.Collections.Generic.List<Group_Relation> query)
        {
            List<models.GroupSmallModel> list = new List<models.GroupSmallModel>();
            foreach (var item in tmplist)
            {
                foreach (var item1 in query.FindAll(x => x.Parent_Group_ID == papa.Id))
                    if (item.Id == item1.Child_Group_ID)
                    {
                        list.Insert(list.Count,item);
                        list.InsertRange(list.Count,ChildDetector(item,tmplist,query));
                        break;
                    }
            }
            return list;
        }

        public override List<GroupSmallModel> GetFilteredGroupSmall(GroupSmallFilter pFilter)
        {
            int OrderNum;
            if ((pFilter.OrderEnum == (GroupSmallOrderEnum)1) && (pFilter.IsOrderDesc == true))
                OrderNum = 1;
            else
            {
                if ((pFilter.OrderEnum == (GroupSmallOrderEnum)1) && (pFilter.IsOrderDesc == false))
                    OrderNum = 2;
                else
                {
                    if ((pFilter.OrderEnum == (GroupSmallOrderEnum)2) && (pFilter.IsOrderDesc == true))
                        OrderNum = 3;
                    else
                    {
                        if ((pFilter.OrderEnum == (GroupSmallOrderEnum)2) && (pFilter.IsOrderDesc == false))
                            OrderNum = 4;
                        else
                        {
                            OrderNum = 0;
                        }
                    }
                }
            }


            /*List<models.GroupSmallModel> tmplist = 
                GetAllGroupsss(OrderNum)
                    .Where(x=>x.Name.IndexOf(pFilter.SearchString, StringComparison.InvariantCultureIgnoreCase)>-1)
                    .ToList();*/
            List<models.GroupSmallModel> tmplist = GetAllGroupsss(OrderNum);
            if (tmplist.Count == 0) return tmplist;
            //List<models.GroupSmallModel> ourlist = GetAllGroupsss(OrderNum);
            List<models.GroupSmallModel> ourlist = new List<models.GroupSmallModel>();
            using (var context = new tpksEntities())
            {
                
                var query = (from qwe in context.Group_Relation select qwe).ToList();
                foreach (var item in tmplist)
                {
                    if (item.Level == 0)
                    {
                        ourlist.Insert(ourlist.Count,item);
                        ourlist.InsertRange(ourlist.Count, ChildDetector(item, tmplist, query));
                    }
                }
            }
            ourlist = ourlist.Where(x => x.Name.IndexOf(pFilter.SearchString, StringComparison.InvariantCultureIgnoreCase) > -1)
                    .ToList();
            if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                return ourlist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
            else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                return ourlist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
            else
                return ourlist;
        }

        public static List<models.GroupSmallModel> GetAllGroupsss(int OrderNum)
        {
            AdminFacadeSql a = new AdminFacadeSql();
            List<models.GroupSmallModel> tmplist = new List<models.GroupSmallModel>();
            try
            {
                using (var context = new tpksEntities())
                {
                    //var rr = context.Group.ToList();
                    switch (OrderNum)
                    {
                        case 0:
                           
                        case 1:
                            {
                                var query1 = (from gr in context.Group orderby gr.Group_Name select gr).ToList();
                                var query = (from grr in context.Group_Relation select grr).ToList();
                                foreach (var item1 in query1)
                                {
                                    models.GroupSmallModel tmpgroup = new models.GroupSmallModel();
                                    tmpgroup.Id = item1.Group_ID;
                                    tmpgroup.Name = item1.Group_Name;
                                    tmpgroup.Count = item1.User.Count;

                                    List<models.FieldModel> fieldlist = new List<models.FieldModel>();
                                    //var Query = (from gr in context.Group_Field where gr.Group_ID == pId select gr).ToList();
                                    var Query = item1.Group_Field.ToList();
                                    foreach (var item in Query)
                                    {
                                        models.FieldModel fieldModel = new models.FieldModel();
                                        fieldModel.Name = item.Field_Name;
                                        fieldModel.Value = item.Field_Value;
                                        models.FieldTypeEnum type = new models.FieldTypeEnum();
                                        type = (models.FieldTypeEnum)item.Field_Type;
                                        fieldModel.Type = type;
                                        if ((item.User_Edit_Flag)[0] == '1')
                                            fieldModel.UserCanEdit = true;
                                        else
                                            fieldModel.UserCanEdit = false;
                                        if ((item.Mandatory_Flag)[0] == '1')
                                            fieldModel.Mandatory = true;
                                        else
                                            fieldModel.Mandatory = false;
                                        fieldlist.Add(fieldModel);
                                    }
                                    tmpgroup.Fields = fieldlist;
                                    int count = 0;
                                    int max_count = 0;
                                    //var query = (from grr in context.Group_Relation select grr).ToList();
                                    int level = FindMaxLevel(item1.Group_ID, context, ref count, ref max_count, query);
                                    //tmpgroup.Level = 0;
                                    tmpgroup.Level = level;
                                    tmplist.Add(tmpgroup);

                                }
                                return tmplist;
                            }
                        case 2:
                            {
                                var query1 = (from gr in context.Group orderby gr.Group_Name descending select gr).ToList();
                                var query = (from grr in context.Group_Relation select grr).ToList();
                                foreach (var item1 in query1)
                                {
                                    models.GroupSmallModel tmpgroup = new models.GroupSmallModel();
                                    tmpgroup.Id = item1.Group_ID;
                                    tmpgroup.Name = item1.Group_Name;
                                    tmpgroup.Count = item1.User.Count;

                                    List<models.FieldModel> fieldlist = new List<models.FieldModel>();
                                    //var Query = (from gr in context.Group_Field where gr.Group_ID == pId select gr).ToList();
                                    var Query = item1.Group_Field.ToList();
                                    foreach (var item in Query)
                                    {
                                        models.FieldModel fieldModel = new models.FieldModel();
                                        fieldModel.Name = item.Field_Name;
                                        fieldModel.Value = item.Field_Value;
                                        models.FieldTypeEnum type = new models.FieldTypeEnum();
                                        type = (models.FieldTypeEnum)item.Field_Type;
                                        fieldModel.Type = type;
                                        if ((item.User_Edit_Flag)[0] == '1')
                                            fieldModel.UserCanEdit = true;
                                        else
                                            fieldModel.UserCanEdit = false;
                                        if ((item.Mandatory_Flag)[0] == '1')
                                            fieldModel.Mandatory = true;
                                        else
                                            fieldModel.Mandatory = false;
                                        fieldlist.Add(fieldModel);
                                    }
                                    tmpgroup.Fields = fieldlist;
                                    int count = 0;
                                    int max_count = 0;
                                    //var query = (from grr in context.Group_Relation select grr).ToList();
                                    int level = FindMaxLevel(item1.Group_ID, context, ref count, ref max_count, query);
                                    //tmpgroup.Level = 0;
                                    tmpgroup.Level = level;
                                    tmplist.Add(tmpgroup);

                                }
                                return tmplist;
                            }
                        case 3:
                            {
                                List<models.GroupSmallModel> tmplist1 = new List<models.GroupSmallModel>();
                                var query1 = (from ug in context.Group select ug).ToList();
                                var query = (from grr in context.Group_Relation select grr).ToList();
                                foreach (var item1 in query1)
                                {
                                    models.GroupSmallModel tmpgroup = new models.GroupSmallModel();
                                    tmpgroup.Id = item1.Group_ID;
                                    tmpgroup.Name = item1.Group_Name;
                                    tmpgroup.Count = item1.User.Count;

                                    List<models.FieldModel> fieldlist = new List<models.FieldModel>();
                                    //var Query = (from gr in context.Group_Field where gr.Group_ID == pId select gr).ToList();
                                    var Query = item1.Group_Field.ToList();
                                    foreach (var item in Query)
                                    {
                                        models.FieldModel fieldModel = new models.FieldModel();
                                        fieldModel.Name = item.Field_Name;
                                        fieldModel.Value = item.Field_Value;
                                        models.FieldTypeEnum type = new models.FieldTypeEnum();
                                        type = (models.FieldTypeEnum) item.Field_Type;
                                        fieldModel.Type = type;
                                        if ((item.User_Edit_Flag)[0] == '1')
                                            fieldModel.UserCanEdit = true;
                                        else
                                            fieldModel.UserCanEdit = false;
                                        if ((item.Mandatory_Flag)[0] == '1')
                                            fieldModel.Mandatory = true;
                                        else
                                            fieldModel.Mandatory = false;
                                        fieldlist.Add(fieldModel);
                                    }
                                    tmpgroup.Fields = fieldlist;
                                    int count = 0;
                                    int max_count = 0;
                                    //var query = (from grr in context.Group_Relation select grr).ToList();
                                    int level = FindMaxLevel(item1.Group_ID, context, ref count, ref max_count, query);
                                    //tmpgroup.Level = 0;
                                    tmpgroup.Level = level;
                                    tmplist1.Add(tmpgroup);
                                }
                                Dictionary<int, int> dic = new Dictionary<int, int>();
                                var query11 = (from ug in context.Group orderby ug.Group_ID select ug).ToList();
                                int q = 0;
                                foreach (var item in query11)
                                {
                                    dic.Add(item.Group_ID, item.User.Count);
                                }
                                ICollection<int> keys = dic.Keys;
                                int count1 = dic.Count;
                                for (int i = 0; i < count1; i++)
                                {
                                    int min = dic[keys.Max()];
                                    int minGroup = 0;
                                    foreach (int j in keys)
                                    {
                                        if (dic[j] <= min)
                                        {
                                            min = dic[j];
                                            minGroup = j;
                                        }
                                    }
                                    tmplist.Add(tmplist1.Find(x=> x.Id.Equals(minGroup)));
                                    dic.Remove(minGroup);
                                }
                                return tmplist;
                            }
                        default:
                            {
                                List<models.GroupSmallModel> tmplist1 = new List<models.GroupSmallModel>();
                                var query1 = (from ug in context.Group select ug).ToList();
                                var query = (from grr in context.Group_Relation select grr).ToList();
                                foreach (var item1 in query1)
                                {
                                    models.GroupSmallModel tmpgroup = new models.GroupSmallModel();
                                    tmpgroup.Id = item1.Group_ID;
                                    tmpgroup.Name = item1.Group_Name;
                                    tmpgroup.Count = item1.User.Count;

                                    List<models.FieldModel> fieldlist = new List<models.FieldModel>();
                                    //var Query = (from gr in context.Group_Field where gr.Group_ID == pId select gr).ToList();
                                    var Query = item1.Group_Field.ToList();
                                    foreach (var item in Query)
                                    {
                                        models.FieldModel fieldModel = new models.FieldModel();
                                        fieldModel.Name = item.Field_Name;
                                        fieldModel.Value = item.Field_Value;
                                        models.FieldTypeEnum type = new models.FieldTypeEnum();
                                        type = (models.FieldTypeEnum)item.Field_Type;
                                        fieldModel.Type = type;
                                        if ((item.User_Edit_Flag)[0] == '1')
                                            fieldModel.UserCanEdit = true;
                                        else
                                            fieldModel.UserCanEdit = false;
                                        if ((item.Mandatory_Flag)[0] == '1')
                                            fieldModel.Mandatory = true;
                                        else
                                            fieldModel.Mandatory = false;
                                        fieldlist.Add(fieldModel);
                                    }
                                    tmpgroup.Fields = fieldlist;
                                    int count = 0;
                                    int max_count = 0;
                                    //var query = (from grr in context.Group_Relation select grr).ToList();
                                    int level = FindMaxLevel(item1.Group_ID, context, ref count, ref max_count, query);
                                    //tmpgroup.Level = 0;
                                    tmpgroup.Level = level;
                                    tmplist1.Add(tmpgroup);
                                }
                                Dictionary<int, int> dic = new Dictionary<int, int>();
                                var query11 = (from ug in context.Group orderby ug.Group_ID select ug).ToList();
                                int q = 0;
                                foreach (var item in query11)
                                {
                                    dic.Add(item.Group_ID, item.User.Count);
                                }
                                ICollection<int> keys = dic.Keys;
                                int count1 = dic.Count;
                                for (int i = 0; i < count1; i++)
                                {
                                    int max = 0;
                                    int maxGroup = 0;
                                    foreach (int j in keys)
                                    {
                                        if (dic[j] >= max)
                                        {
                                            max = dic[j];
                                            maxGroup = j;
                                        }
                                    }
                                    tmplist.Add(tmplist1.Find(x => x.Id.Equals(maxGroup)));
                                    dic.Remove(maxGroup);
                                }
                                return tmplist;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.ToString());

                return null;
            }
        }


        public override Dictionary<int, string> GetAvaliableParents(int pIdGroup)
        {
            Dictionary<int, string> dic = new Dictionary<int, string>();
            using (var context = new tpksEntities())
            {
                if (context.Group.Find(pIdGroup) == null)
                    return null;
                var Query = (from gr in context.Group_Relation select gr).ToList();
                var Query1 = (from gr in context.Group select new { gr.Group_ID, gr.Group_Name }).ToList();
                foreach (var item in Query1)
                {
                    dic.Add(item.Group_ID, item.Group_Name);
                }
                dic.Remove(pIdGroup);
                GetAvaliableParentsHelp1(pIdGroup, context, Query, ref dic);
                GetAvaliableParentsHelp2(pIdGroup, context, Query, ref dic);
            }
            return dic;
        }

        private static void GetAvaliableParentsHelp1(int id, tpksEntities context, System.Collections.Generic.List<Group_Relation> query, ref Dictionary<int, string> dic)
        {
            //var query = from grr in context.Group_Relation where grr.Child_Group_ID == id select grr;
            var Query = query.Where(s => s.Child_Group_ID == id);
            foreach (var item in Query)
            {
                dic.Remove(item.Parent_Group_ID);
                GetAvaliableParentsHelp1(item.Parent_Group_ID, context, query, ref dic);
            }
        }

        private static void GetAvaliableParentsHelp2(int id, tpksEntities context, System.Collections.Generic.List<Group_Relation> query, ref Dictionary<int, string> dic)
        {
            //var query = from grr in context.Group_Relation where grr.Child_Group_ID == id select grr;
            var Query = query.Where(s => s.Parent_Group_ID == id);
            foreach (var item in Query)
            {
                dic.Remove(item.Child_Group_ID);
                GetAvaliableParentsHelp2(item.Child_Group_ID, context, query, ref dic);
            }
        }


        public override models.GroupModel GetGroupById(int pId)
        {
            try
            {
                //List<GroupModel> tmplist = new List<GroupModel>();
                models.GroupModel tmpgroup = new models.GroupModel();
                using (var context = new tpksEntities())
                {
                    if (pId == -1)
                    {
                        tmpgroup.SelectedParents = context.Group
                            .ToDictionary(rd => rd.Group_ID, rd => rd.Group_Name);
                    }
                    else
                    {
                        var rd = (context.Group.FirstOrDefault(x => x.Group_ID == pId));
                        tmpgroup.Id = rd.Group_ID;
                        tmpgroup.Name = rd.Group_Name;
                        List<models.FieldModel> fieldlist = new List<models.FieldModel>();
                        var Query = (rd.Group_Field).ToList();
                        foreach (var item in Query)
                        {
                            models.FieldModel fieldModel = new models.FieldModel();
                            /*fieldModel.Name = item.Field_Name;
                            fieldModel.FieldId = item.Field_ID;
                            fieldModel.Value = item.Field_Value;
                            models.FieldTypeEnum type = new models.FieldTypeEnum();
                            type = (models.FieldTypeEnum)item.Field_Type;
                            fieldModel.Type = type;
                            if ((item.User_Edit_Flag)[0] == '1')
                                fieldModel.UserCanEdit = true;
                            else
                                fieldModel.UserCanEdit = false;
                            if ((item.Mandatory_Flag)[0] == '1')
                                fieldModel.Mandatory = true;
                            else
                                fieldModel.Mandatory = false;*/
                            dbfieldtofield(fieldModel, item);
                            fieldlist.Add(fieldModel);
                        }
                        tmpgroup.Fields = fieldlist;
                        var Query1 = from gr in context.Group join par in context.Group_Relation on gr.Group_ID equals par.Parent_Group_ID where par.Child_Group_ID == pId select gr;
                        Dictionary<int, string> dic1 = new Dictionary<int, string>();
                        foreach (var item in Query1)
                        {
                            dic1.Add(item.Group_ID, item.Group_Name);
                        }
                        tmpgroup.SelectedParents = dic1;

                    }
                }
                //tmplist.Add(tmpgroup);        ???
                return tmpgroup;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.ToString());
                return null;
            }

            //throw new NotImplementedException();
        }

        public override void DeleteParentForGroup(int pIdParent, int pIdGroup)//vet
        {
            using (var context = new tpksEntities())
            {
                context.Group_Relation.Remove(context.Group_Relation.FirstOrDefault(x => x.Parent_Group_ID.Equals(pIdParent) && x.Child_Group_ID.Equals(pIdGroup)));
                context.SaveChanges();
            }
        }

        public override void AddParentForGroup(int pIdParent, int pIdGroup)//vet
        {
            using (var context = new tpksEntities())
            {
                var newGroupRelation = new Group_Relation()
                {
                    Child_Group_ID = pIdGroup,
                    Parent_Group_ID = pIdParent
                };
                context.Group_Relation.Add(newGroupRelation);
                context.SaveChanges();
            }
        }

        public override void UpdateGroup(GroupModel pGroupModel)//i
        {
            using (var context = new tpksEntities())
            {
                /*Group_Relation grrr = new Group_Relation();
                grrr.Child_Group_ID = 1010;
                grrr.Parent_Group_ID =1009;
                context.Group_Relation.Add(grrr);
                context.SaveChanges();*/
                //if (context.Group.Find(pGroupModel.Id) == null)
                //    pGroupModel.Id=AddGroup(pGroupModel.Name);
                Group nGroup = context.Group.Find(pGroupModel.Id);
                context.Group_Relation.RemoveRange(context.Group_Relation.Where(x=>x.Child_Group_ID==nGroup.Group_ID));
                var gr = context.Group_Relation.Where(x=>x.Child_Group_ID==nGroup.Group_ID).ToList();
                foreach (var q in pGroupModel.SelectedParents)
                {
                    bool less = true;
                    foreach (var w in gr)
                    {
                        if (w.Parent_Group_ID == q.Key)
                        {
                            less = false;
                        }
                    }
                    if (less)
                    {
                        Group_Relation grr = new Group_Relation();
                        grr.Child_Group_ID = nGroup.Group_ID;
                        grr.Parent_Group_ID = q.Key;
                        context.Group_Relation.Add(grr);
                    }
                }
                foreach (var w in gr)
                {
                    bool more = true;
                    foreach (var q in pGroupModel.SelectedParents)
                    {
                        if (w.Parent_Group_ID == q.Key)
                        {
                            more = false;
                        }
                    }
                    if (more)
                        context.Group_Relation.Remove(w);
                } 
                nGroup.Group_Name = pGroupModel.Name;
                var gp = nGroup.Group_Field.ToList();
                foreach (var qfield in pGroupModel.Fields)
                {
                    bool less = true;
                    foreach (var wdbfield in gp.Where(wdbfield => wdbfield.Field_ID == qfield.FieldId))
                    {
                        Fieldtodbfield(wdbfield,qfield);
                        less = false;
                    }
                    if (less)
                    {
                        Group_Field wdbfield=new Group_Field();
                        Fieldtodbfield(wdbfield,qfield);
                        wdbfield.Group_ID = nGroup.Group_ID;
                        context.Group_Field.Add(wdbfield);
                    }
                }
                foreach (var wdbfield in gp)
                {
                    bool more= true;
                    foreach (var qfield in pGroupModel.Fields)
                    {
                        if (wdbfield.Field_ID == qfield.FieldId)
                        {
                            more = false;
                        }
                    }
                    if (more)
                        context.Group_Field.Remove(wdbfield);
                    if (pGroupModel.Fields.Any(x=>x.FieldId==wdbfield.Field_ID))
                        context.Group_Field.Remove(wdbfield);
                }
                context.SaveChanges();
                
            }
        }

        public override void RemoveGroup(int pId)//i
        {
            using (var context = new tpksEntities())
            {
               // var allgroups = context.Group.ToList();
                Group g=context.Group.Find(pId);
                if (g!=null)
                context.Group.Remove(g);
                context.SaveChanges();
            }
        }

        public override int AddGroup(string pGroupName)//i
        {
            using (var context = new tpksEntities())
            {
                Group nGroup=new Group();
                nGroup.Group_Name = pGroupName;
                nGroup.Parent_Flag = "0";
                context.Group.Add(nGroup);
                context.SaveChanges();
                int i=context.Group.FirstOrDefault(x => x.Group_Name.Equals(pGroupName)).Group_ID;
                return i;
            }
        }

        public override Dictionary<int, string> GetAvaliableGroupsForUser(int pUserId)
        {
            Dictionary<int, string> dic = new Dictionary<int, string>();
            try
            {
                using (var context = new tpksEntities())
                {
                    var query = from q in context.Group  select q;
                    foreach (var item in query)
                    {
                        dic.Add(item.Group_ID,item.Group_Name);
                    }
                    var rd = (context.User.FirstOrDefault(x => x.User_ID == pUserId).Group).ToList();
                    foreach (var item in rd)
                        {
                            dic.Remove(item.Group_ID);
                        }
                }
                return dic;
            }
            catch (Exception ex)
            {
                Dictionary<int, string> dic1 = new Dictionary<int, string>();
                return dic1;
            }
        }
        #endregion

        #region Users

        public override UserModel GetUserWithGroupsList(int pIdUser)
        {
            using (var context = new tpksEntities())
            {
                //var qq = context.User.ToList();
                var user = context.User.FirstOrDefault(x => x.User_ID.Equals(pIdUser));
                if (user != null)
                {
                    UserModel tmp = new UserModel();
                    tmp.Id = user.User_ID;
                    tmp.FirstName = user.First_Name;
                    tmp.SecondName = user.Second_Name;
                    tmp.Surname = user.Last_Name;
                    tmp.UserName = user.Login;
                    foreach (var usergroups in user.Group)
                    {
                        GroupModel group = new GroupModel();
                        group.Id = usergroups.Group_ID;
                        group.Name = usergroups.Group_Name;
                        tmp.UserGroups.Add(group);
                    }
                    return tmp;
                }
                return null;
                
            }
        }


        public override List<UserModel> GetFilteredUsers(UserFilter pFilter)
        {
            try
            {
                int OrderNum;
                if ((pFilter.OrderEnum == (UserOrderEnum)1) && (pFilter.IsOrderDesc == true))
                    OrderNum = 1;
                else
                {
                    if ((pFilter.OrderEnum == (UserOrderEnum)1) && (pFilter.IsOrderDesc == false))
                        OrderNum = 2;
                    else
                    {
                        if ((pFilter.OrderEnum == (UserOrderEnum)2) && (pFilter.IsOrderDesc == true))
                            OrderNum = 3;
                        else
                        {
                            if ((pFilter.OrderEnum == (UserOrderEnum)2) && (pFilter.IsOrderDesc == false))
                                OrderNum = 4;
                            else
                            {
                                OrderNum = 0;
                            }
                        }
                    }
                }
                using (var context = new tpksEntities())
                {
                    var qqq = context.Group.ToList();
                    var rrr = context.Group_Field.ToList();
                    List<UserModel> tmplist= new List<UserModel>();
                    switch (OrderNum)
                    {
                        case 2:
                        {
                            //var query = (from u in context.User orderby u.Login select u).ToList();
                            var query =
                                (from u in context.User
                                 join ugpv in context.User_Grp_Prm_Value on u.User_ID equals ugpv.User_ID
                                 join gf in context.Group_Field on ugpv.Field_ID equals gf.Field_ID
                                 where gf.Group_ID == pFilter.IdGroup
                                 orderby u.Login
                                 select u).Distinct().OrderBy(x => x.Login).ToList();
                            if (pFilter.IdGroup == -1)
                            {
                               query = (from u in context.User orderby u.Login select u).ToList();
                            }
                            foreach (var item in query)
                            {
                                UserModel user = new UserModel();
                                dbusertouser(user, item);
                                tmplist.Add(user);
                            }
                            tmplist=tmplist.Where(x=>x.UserName.IndexOf(pFilter.SearchString, StringComparison.InvariantCultureIgnoreCase)>-1)
                    .ToList();
                            if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                            else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                            else
                                return tmplist;
                        }
                        case 1:
                        {
                            var query =
                                (from u in context.User
                                 join ugpv in context.User_Grp_Prm_Value on u.User_ID equals ugpv.User_ID
                                 join gf in context.Group_Field on ugpv.Field_ID equals gf.Field_ID
                                 where gf.Group_ID == pFilter.IdGroup
                                 orderby u.Login descending 
                                 select u).Distinct().OrderByDescending(x=>x.Login).ToList();
                            if (pFilter.IdGroup == -1)
                            {
                                query = (from u in context.User orderby u.Login descending select u).ToList();
                            }

                            //var query = (from u in context.User orderby u.Login descending select u).ToList();
                            foreach (var item in query)
                            {
                                UserModel user = new UserModel();
                                dbusertouser(user, item);
                                tmplist.Add(user);
                            }
                            tmplist = tmplist.Where(x => x.UserName.IndexOf(pFilter.SearchString, StringComparison.InvariantCultureIgnoreCase) > -1)
                    .ToList();
                            tmplist.OrderBy(x => x.UserName);
                            if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                            else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                            else
                                return tmplist;
                            
                        }
                        case 3:
                        {
                            var query =
                                (from u in context.User
                                 join ugpv in context.User_Grp_Prm_Value on u.User_ID equals ugpv.User_ID
                                 join gf in context.Group_Field on ugpv.Field_ID equals gf.Field_ID
                                 where gf.Group_ID == pFilter.IdGroup
                                 orderby u.Birth_Date
                                 select u).Distinct().OrderBy(x => x.Birth_Date).ToList();
                            if (pFilter.IdGroup == -1)
                            {
                                query = (from u in context.User orderby u.Birth_Date select u).ToList();
                            }
                            //var query = (from u in context.User orderby u.Birth_Date select u).ToList();
                            foreach (var item in query)
                            {
                                UserModel user = new UserModel();
                                dbusertouser(user, item);
                                tmplist.Add(user);
                            }
                            tmplist = tmplist.Where(x => x.UserBd.IndexOf(pFilter.SearchString, StringComparison.InvariantCultureIgnoreCase) > -1)
                    .ToList();
                            if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                            else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                            else
                                return tmplist;
                        }
                        default:
                        {
                            var query =
                                (from u in context.User
                                 join ugpv in context.User_Grp_Prm_Value on u.User_ID equals ugpv.User_ID
                                 join gf in context.Group_Field on ugpv.Field_ID equals gf.Field_ID
                                 where gf.Group_ID == pFilter.IdGroup
                                 orderby u.Birth_Date descending
                                 select u).Distinct().OrderByDescending(x=>x.Birth_Date).ToList();
                            if (pFilter.IdGroup == -1)
                            {
                                query = (from u in context.User orderby u.Birth_Date descending select u).ToList();
                            }
                            //var query = (from u in context.User orderby u.Birth_Date descending select u).ToList();
                            foreach (var item in query)
                            {
                                UserModel user = new UserModel();
                                dbusertouser(user, item);
                                tmplist.Add(user);
                            }
                            tmplist = tmplist.Where(x => x.UserBd.IndexOf(pFilter.SearchString, StringComparison.InvariantCultureIgnoreCase) > -1)
                    .ToList();
                            if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                            else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                            else
                                return tmplist;
                            
                        }
                    }
                    
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.ToString());

                return null;
            }
        }


        public override void SetUserIsAdmin(int pIdUser, bool pIsAdmin)
        {
            using (var context = new tpksEntities())
            {                
                var user = context.User.FirstOrDefault(x => x.User_ID.Equals(pIdUser));
                if (user != null)
                {
                    if (pIsAdmin) user.Admin_Flag = "1";
                    if (!pIsAdmin) user.Admin_Flag = "0";
                    context.SaveChanges();
                }
            }
        }

        public override Dictionary<int,string> GetGroupsByUser(int pIdUser)
        {
            using (var context = new tpksEntities())
            {
                Dictionary<int, string> dic = new Dictionary<int, string>();
                var groups = context.User.Find(pIdUser).Group.ToList();
                foreach (var group in groups)
                {
                    dic.Add(group.Group_ID, group.Group_Name);
                }
                return dic;
            }
        }
        public override void RemoveGroupFromUser(int pIdUser, int pIdGroup)
        {
            using (var context = new tpksEntities())
            {
        //        var allusers = context.User.ToList();
        //        var allgroups = context.Group.ToList();

                var user = context.User.Include("User_Grp_Prm_Value").FirstOrDefault(x => x.User_ID.Equals(pIdUser));
                var group = context.Group.Include("Group_Field").FirstOrDefault(x => x.Group_ID.Equals(pIdGroup));
                if (user != null)
                {
                    user.Group.Remove(group);
                  /*  foreach (var p in group.Group_Field)
                    {
                      context.User_Grp_Prm_Value.Remove(user.User_Grp_Prm_Value.FirstOrDefault(x => x.Field_ID.Equals(p.Field_ID)));
                    }   */
                    
                 }
               
                 context.SaveChanges();
            }
        }

        public override void AddGroupToUser(int pIdUser, int pIdGroup)
        {
            using (var context = new tpksEntities())
            {
     //           var allusers = context.User.ToList();
     //           var allgroups = context.Group.ToList();
     //           var usparam = context.User_Grp_Prm_Value.ToList();

                var user = context.User.FirstOrDefault(x => x.User_ID.Equals(pIdUser));
                var group = context.Group.Include("Group_Field").FirstOrDefault(x => x.Group_ID.Equals(pIdGroup));
              
                if (user != null)
                {
                    user.Group.Add(group);
                  /*  foreach (var p in group.Group_Field)
                    {
                        User_Grp_Prm_Value tmp = new User_Grp_Prm_Value();
                        tmp.User_ID = user.User_ID;
                        tmp.Field_ID = p.Field_ID;
                        tmp.Field_Value = "";
                        context.User_Grp_Prm_Value.Add(tmp);
                    }
 */               }
                context.SaveChanges();
            }
        }

        public override UserModel GetUserById(int pUserId)
        {
            using (var context = new tpksEntities())
            {
                
                User dbuser;
                if ((dbuser = context.User.FirstOrDefault(x => x.User_ID.Equals(pUserId))) != null)
                {
                    UserModel user = new UserModel();
                    dbusertouser(user, dbuser);
                    var qqdbUser = context.User.Include("User_Grp_Prm_Value").Include("User_Grp_Prm_Value.Group_Field").Include("User_Grp_Prm_Value.Group_Field.Group").Where(x => x.User_ID == pUserId).ToList();
                    //var zzz = context.User.Include("User_Grp_Prm_Value").Include("Group_Field").ToList();

                    var allgroups = context.Group.ToList();
                    var group_relation = context.Group_Relation.ToList();
                    var groupfields = dbuser.User_Grp_Prm_Value.ToList();
                    var groups = dbuser.Group.ToList();
                    var fields = context.Group_Field.ToList();
                    var fieldvalues = context.User_Grp_Prm_Value.Where(x => x.User_ID == user.Id);
                    bool findparents = true;
                    //context.Group_Relation.Remove(group_relation[0]);
                    //context.SaveChanges();
                    while (findparents)
                    {
                        findparents = false;
                        int groupc = groups.Count;
                        int i = 0;
                        while (i < groupc)
                        {
                            var q = groups[i];
                            for (int a = group_relation.Count - 1; a > -1; a--)
                            {
                                var w = group_relation[a];
                                if (w.Child_Group_ID == q.Group_ID)
                                {
                                    group_relation.Remove(w);
                                    bool flag2 = false;
                                    foreach (var e in groups)
                                    {
                                        if (e.Group_ID == w.Parent_Group_ID)
                                            flag2 = true;
                                    }
                                    if (!flag2)
                                    {
                                        groupc++;
                                        groups.Add(context.Group.Find(w.Parent_Group_ID));
                                        findparents = true;
                                    }
                                }
                            }
                            i++;
                        }
                    }
                    foreach (var q in groups)
                    {
                        GroupModel group;
                        user.UserGroups.Add((group = new GroupModel()
                        {
                            Id = q.Group_ID,
                            Name = q.Group_Name
                        }));
                        foreach (var w in fields)
                        {
                            if (w.Group_ID == q.Group_ID)
                            {
                                FieldModel field = new FieldModel();
                                dbfieldtofield(field, w);
                                group.Fields.Add(field);
                                bool findval = false;
                                foreach (var e in groupfields)
                                {
                                    if ((e.Field_ID == w.Field_ID) && (e.User_ID == user.Id))
                                    {
                                        findval = true;
                                        if (w.Field_Type == 4)
                                        {
                                            field.SelectedValues.Add(int.Parse(e.Field_Value));
                                            field.Value = field.AvaliableValues[field.SelectedValues[0]];
                                        }
                                        else if (w.Field_Type == 5)
                                        {
                                            field.Value = "";
                                            String[] ms = e.Field_Value.Split(',');
                                            for (int i = 0; i < ms.Count(); i++)
                                            {
                                                field.SelectedValues.Add(int.Parse(ms[i]));
                                                if (i != 0)
                                                    field.Value = field.Value + ",";
                                                field.Value = field.Value + field.SelectedValues[i].ToString();
                                            }
                                        }
                                        else
                                        {
                                            field.Value = e.Field_Value;
                                        }
                                    }
                                }
                                if (!findval)
                                {
                                    User_Grp_Prm_Value ugpv = new User_Grp_Prm_Value();
                                    ugpv.Field_ID = w.Field_ID;
                                    ugpv.User_ID = (int)user.Id;
                                    ugpv.Field_Value = "0";
                                    context.User_Grp_Prm_Value.Add(ugpv);
                                    if (((int)field.Type == 4) || (((int)field.Type == 5)))
                                    {
                                        field.Value = field.AvaliableValues[0];
                                        field.SelectedValues.Add(0);
                                    }
                                }
                            }
                        }
                    }
                    context.SaveChanges();
                    return user;
                }
                else
                {
                    return null;
                }
            }
        }
        static char[, ,] arraySymb = new char[64, 64, 64];
        const string chararr = @"abcdefghijklmnopqrstyvwxyz1234567890ABCDEFGHIJKLMNOPQRSTYVWXYZ";


        private void GenerateTable()
        {
            Random rnd = new Random(64);
            for (int i = 0; i < 64; i++)
            {
                for (int j = 0; j < 64; j++)
                {
                    for (int k = 0; k < 64; k++)
                    {
                        // генерим случайное число и по нему выбираем символ
                        arraySymb[i, j, k] = chararr[rnd.Next(chararr.Length)];
                    }
                }
            }
        }
        private string GeneratePassword(int Count)
        //формирует пароль длиной Count
        {
            string result = ""; // результат - готовый пароль

            GenerateTable();

            // генератор случайных чисел
            Random rndgen = new Random();
            // int maxval = newsymb.Length;
            for (int i = 0; i < Count; i++)
            {
                // случайное число
                int idx1 = (rndgen.Next(63)) + 1;
                int idx2 = (rndgen.Next(63)) + 1;
                int idx3 = (rndgen.Next(30)) * 2;
                // получаем сивол из трёхмерного массива
                char rand = arraySymb[idx1, idx2, idx3];
                // записываем в буфер
                string buf = rand.ToString();
                // регистр симоволов, тоже случайно
                if (rndgen.Next(3) > 1)
                {
                    buf = buf.ToUpper();
                }
                // буфер помещаем в результирующую строку
                result += buf;
            }
            return result;
        }
        public override int AddUser(UserModel pModel)
        {
            using (var context = new tpksEntities())
            {
                //for(int i=0;i<10000;i++)
                //if (context.User.FirstOrDefault(x => x.Login.Equals(pModel.UserName)) != null) 
                  //  throw new Exception("Пользователь с таким именем уже существует");
       //         var allgroups = context.Group.ToList();
       //         var allusers = context.Group.ToList();
                //var q = context.User.ToList();
                var tmp = new User();
                usertodbuser(tmp,pModel);
                tmp.Admin_Flag = "0";
                tmp.Password = GeneratePassword(15);
                for (int i = 0; i < pModel.UserGroups.Count; i++)
                {
                    tmp.Group.Add(context.Group.Find(pModel.UserGroups[i].Id));
                    for (int j = 0; j < pModel.UserGroups[i].Fields.Count; j++)
                    {
                        User_Grp_Prm_Value groupField = new User_Grp_Prm_Value();
                        groupField.Field_ID = pModel.UserGroups[i].Fields[j].FieldId;
                        groupField.User_ID = tmp.User_ID;
                        groupField.Field_Value = pModel.UserGroups[i].Fields[j].Value;
                        tmp.User_Grp_Prm_Value.Add(groupField);
                    }
                }
                /*var qqq = new User
                {
                    First_Name = "qqq",
                    Group = pModel.UserGroups.Select(x => context.Group.Find(x))
                };*/
                context.User.Add(tmp);
                context.SaveChanges();
                var mail = new MailMessage("k01-224@mail.ru",pModel.UserEmail,"Регистрация в нашей клевой системе", 
                    String.Format("Спасибо за регистрацию в нашей клевой системе, {0}! Держите пароль: {1}",
                        pModel.UserName, tmp.Password));
                using (var sc = new SmtpClient("smtp.mail.ru", 25))
                {
                    sc.EnableSsl = true;
                    sc.DeliveryMethod = SmtpDeliveryMethod.Network;
                    sc.UseDefaultCredentials = false;
                    sc.Credentials = new NetworkCredential("k01-224@mail.ru", "k20102015");
                    sc.Send(mail);
                }
                return context.User.FirstOrDefault(x => x.Login.Equals(pModel.UserName)).User_ID;
            }
        }
        
        public override void UpdateUser(UserModel pUserModel)    
        {
            using (var context = new tpksEntities())
            {

         //       var allusers = context.User.ToList();
                

                var user = context.User.Include("Group").Include("User_Grp_Prm_Value").FirstOrDefault(x => x.User_ID.Equals((int)pUserModel.Id));
         //       var allgroups = context.Group.ToList();

                usertodbuser(user,pUserModel);

                for (int i = user.Group.Count; i > 0; i--)
                {
                    var userGroups = user.Group.First();
                    user.Group.Remove(userGroups);
                    for (int j = userGroups.Group_Field.Count; j > 0; j--)
                    {
                        var groupFields = userGroups.Group_Field.First();
                      user.User_Grp_Prm_Value.Remove(user.User_Grp_Prm_Value.FirstOrDefault(x => x.Field_ID.Equals(groupFields.Field_ID)));
                    }
                }
                while (user.User_Grp_Prm_Value.Any(x=>x.User_ID==pUserModel.Id))
                    user.User_Grp_Prm_Value.Remove(user.User_Grp_Prm_Value.FirstOrDefault(x=>x.User_ID==pUserModel.Id));
                for (int i = 0; i < pUserModel.UserGroups.Count; i++)
                {
                    user.Group.Add(context.Group.Find(pUserModel.UserGroups[i].Id));
                    for (int j = 0; j < pUserModel.UserGroups[i].Fields.Count; j++)
                    {
                        User_Grp_Prm_Value groupField = new User_Grp_Prm_Value();
                        groupField.Field_ID = pUserModel.UserGroups[i].Fields[j].FieldId;
                        groupField.User_ID = user.User_ID;
                        groupField.Field_Value = "";
                        if ((pUserModel.UserGroups[i].Fields[j].SelectedValues.Count>0))
                        {
                            groupField.Field_Value = string.Join(",",pUserModel.UserGroups[i].Fields[j].SelectedValues);
                            //for (int k = 0; k < pUserModel.UserGroups[i].Fields[j].SelectedValues.Count; k++)
                            //{
                            //    groupField.Field_Value = groupField.Field_Value + "," +
                            //                             pUserModel.UserGroups[i].Fields[j].AvaliableValues
                            //                                 .FirstOrDefault(x => x.Key.Equals(k));
                            //}
                        }
                        else
                        {
                           groupField.Field_Value = pUserModel.UserGroups[i].Fields[j].Value; 
                        }
                       
                        user.User_Grp_Prm_Value.Add(groupField);
                    }

                   
                }
                context.SaveChanges();
            }
        }

        public override void RemoveUser(int pId)
        {
            using (var context = new tpksEntities())
            {
                context.User.Remove(context.User.FirstOrDefault(x => x.User_ID.Equals(pId)));
                context.SaveChanges();
            }
        }

        #endregion

        public override bool UserNotExists(string userName)
        {
            using (var context = new tpksEntities())
                return context.User.All(x => !x.Login.Equals(userName, StringComparison.InvariantCultureIgnoreCase));
        }
        public override bool GroupNotExists(string userName)
        {
            using (var context = new tpksEntities())
                return context.Group.All(x => !x.Group_Name.Equals(userName, StringComparison.InvariantCultureIgnoreCase));
        }
        public override bool RouteNotExists(string routeName)
        {
            using (var context = new tpksEntities())
                return context.Way.All(x => !x.Way_Name.Equals(routeName, StringComparison.InvariantCultureIgnoreCase));
        }
        #region Routes

        public override int CreateRoute(RouteModel pRouteModel)
        {
            using (var context = new tpksEntities())
            {
                var tmp = new Way();
                tmp.Way_ID = pRouteModel.RouteId;
                tmp.Way_Name = pRouteModel.RouteName;
                tmp.Creation_Date = DateTime.Now;
                context.Way.Add(tmp);
                context.SaveChanges();
                tmp=context.Way.FirstOrDefault(x=>x.Way_Name.Equals(pRouteModel.RouteName));
                //tmp.Creation_Date = pRouteModel.время;
                for (int i = 0; i < pRouteModel.Points.Count; i++)
                {
                    Step stepField = new Step();
                    stepField.Way_ID = pRouteModel.Points[i].Step.Way_ID;
                    stepField.Step_Nmbr = i;
                    stepField.Step_Name = pRouteModel.Points[i].Step.Step_Name;
                    stepField.Way = tmp;
                    tmp.Step.Add(stepField);
                    context.SaveChanges();
                    int qqq = stepField.Step_Nmbr;
                    stepField = context.Step.FirstOrDefault(x => ((x.Way_ID == tmp.Way_ID)&&(x.Step_Nmbr== qqq)));
                    foreach (var q in pRouteModel.Points[i].Params)
                    {
                        var qq = q;
                        qq.Step_ID = stepField.Step_ID;
                        context.Step_Prm.Add(qq);
                        context.SaveChanges();
                    }
                    foreach (var q in pRouteModel.Points[i].Fields)
                    {
                        if(q.Visible_Flag[0]=='F')
                        q.Visible_Flag = "0";
                        else
                            q.Visible_Flag = "1";
                        if (q.Editable_Falg[0] == 'F')
                            q.Editable_Falg = "0";
                        else
                            q.Editable_Falg = "1";
                        q.Field_Value = "";
                        stepField.Step_Field.Add(q);
                        context.SaveChanges();
                    }

                }
                //context.SaveChanges();
                //tmp = context.Way.FirstOrDefault(x => x.Way_Name.Equals(pRouteModel.RouteName));
                for (int i = 0; i < pRouteModel.WayParams.Count; i++)
                {
                    Way_Prm stepWayPrm = new Way_Prm();
                    stepWayPrm.Cntr = pRouteModel.WayParams[i].Cntr;
                    stepWayPrm.Way_ID = pRouteModel.WayParams[i].Way_ID;
                    stepWayPrm.Prm_Name = pRouteModel.WayParams[i].Prm_Name;
                    stepWayPrm.Prm_Value = pRouteModel.WayParams[i].Prm_Value;
                    stepWayPrm.Field_Type = pRouteModel.WayParams[i].Field_Type;
                    stepWayPrm.Way = pRouteModel.WayParams[i].Way;
                    tmp.Way_Prm.Add(stepWayPrm);
                }
                //tmp.Doc = pRouteModel.WayParams.FirstOrDefault().Way.Doc;
                //context.Way.Add(tmp);
                context.SaveChanges();
                //return context.Way.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)).Way_ID;
                return tmp.Way_ID;
            }

        }

        public override int UpdateRoute(RouteModel pRouteModel)
        {
            using (var context = new tpksEntities())
            {
                var way =
                    context.Way.Include("way_Prm")
                        .Include("step")
                        .FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId));
                //way.Way_ID = pRouteModel.RouteId;
                //way.Way_Name = pRouteModel.RouteName;

                var numberTurn = way.Way_Prm.Count;

                for (int i = 0; i < numberTurn; i++)
                {
                    context.Way_Prm.Remove(way.Way_Prm.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)));
                    //way.Way_Prm.Remove(way.Way_Prm.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)));
                    context.SaveChanges();
                }

                numberTurn = way.Step.Count;
                for (int i = 0; i < numberTurn; i++)
                {
                    context.Step.Remove(way.Step.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)));
                    //way.Step.Remove(way.Step.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)));
                    context.SaveChanges();
                }
                

                /*for (int i = 0; i < way.Step.Count; i++)
                {
                    var wayStep = context.Step.First();
                    //way.Step.Remove(wayStep);
                    for (int j = 0; j < wayStep.Step_Prm.Count; j++)
                    {
                        wayStep.Step_Prm.Remove(
                            wayStep.Step_Prm.FirstOrDefault(x => x.Step_ID.Equals(wayStep.Step_ID)));
                    }
                    for (int j = 0; j < wayStep.Step_Field.Count; j++)
                    {
                        wayStep.Step_Field.Remove(
                            wayStep.Step_Field.FirstOrDefault(x => x.Step_ID.Equals(wayStep.Step_ID)));
                    }
                 
                    context.SaveChanges();
                    way.Step.Remove(wayStep);
                    context.SaveChanges();
                }*/

                way.Way_Name = pRouteModel.RouteName;
                way.Creation_Date = DateTime.Now;
                context.SaveChanges();

                for (int i = 0; i < pRouteModel.Points.Count; i++)
                {
                    Step stepField = new Step();
                    stepField.Way_ID = pRouteModel.Points[i].Step.Way_ID;
                    stepField.Step_Nmbr = i;
                    stepField.Step_Name = pRouteModel.Points[i].Step.Step_Name;
                    stepField.Way = way;
                    way.Step.Add(stepField);
                    context.SaveChanges();
                    int qqq = stepField.Step_Nmbr;
                    stepField = context.Step.FirstOrDefault(x => ((x.Way_ID == way.Way_ID) && (x.Step_Nmbr == qqq)));
                    foreach (var q in pRouteModel.Points[i].Params)
                        stepField.Step_Prm.Add(q);
                    context.SaveChanges();
                    foreach (var q in pRouteModel.Points[i].Fields)
                    {
                        if (q.Visible_Flag[0] == 'F')
                            q.Visible_Flag = "0";
                        else
                            q.Visible_Flag = "1";
                        if (q.Editable_Falg[0] == 'F')
                            q.Editable_Falg = "0";
                        else
                            q.Editable_Falg = "1";
                        q.Field_Value = "";
                        stepField.Step_Field.Add(q);
                    }
                    context.SaveChanges();
                }
                //context.SaveChanges();
                //tmp = context.Way.FirstOrDefault(x => x.Way_Name.Equals(pRouteModel.RouteName));
                for (int i = 0; i < pRouteModel.WayParams.Count; i++)
                {
                    Way_Prm stepWayPrm = new Way_Prm();
                    stepWayPrm.Cntr = pRouteModel.WayParams[i].Cntr;
                    stepWayPrm.Way_ID = pRouteModel.WayParams[i].Way_ID;
                    stepWayPrm.Prm_Name = pRouteModel.WayParams[i].Prm_Name;
                    stepWayPrm.Prm_Value = pRouteModel.WayParams[i].Prm_Value;
                    stepWayPrm.Field_Type = pRouteModel.WayParams[i].Field_Type;
                    stepWayPrm.Way = pRouteModel.WayParams[i].Way;
                    way.Way_Prm.Add(stepWayPrm);
                }
                //tmp.Doc = pRouteModel.WayParams.FirstOrDefault().Way.Doc;
                //context.Way.Add(tmp);
                context.SaveChanges();
                //way = context.Way.FirstOrDefault(x => x.Way_Name.Equals(pRouteModel.RouteName));
                //return context.Way.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)).Way_ID;
                return 0;

                //for (int i = 0; i < way.Step.Count; i++)
                //{
                //    way.Step.Remove(way.Step.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)));
                //}
                //for (int i = 0; i < pRouteModel.Points.Count; i++)
                //{
                //    Step stepField = new Step();
                //    stepField.Step_ID = pRouteModel.Points[i].Step.Step_ID;
                //    stepField.Way_ID = pRouteModel.Points[i].Step.Way_ID;
                //    stepField.Step_Nmbr = pRouteModel.Points[i].Step.Step_Nmbr;
                //    stepField.Step_Name = pRouteModel.Points[i].Step.Step_Name;
                //    stepField.Step_Prm = pRouteModel.Points[i].Params;
                //    stepField.Step_Field = pRouteModel.Points[i].Fields;
                //    stepField.Way = pRouteModel.Points[i].Step.Way;
                //    way.Step.Add(stepField);
                //}
                //for (int i = 0; i < pRouteModel.WayParams.Count; i++)
                //{
                //    Way_Prm stepWayPrm = new Way_Prm();
                //    stepWayPrm.Cntr = pRouteModel.WayParams[i].Cntr;
                //    stepWayPrm.Way_ID = pRouteModel.WayParams[i].Way_ID;
                //    stepWayPrm.Prm_Name = pRouteModel.WayParams[i].Prm_Name;
                //    stepWayPrm.Prm_Value = pRouteModel.WayParams[i].Prm_Value;
                //    stepWayPrm.Field_Type = pRouteModel.WayParams[i].Field_Type;
                //    stepWayPrm.Way = pRouteModel.WayParams[i].Way;
                //    way.Way_Prm.Add(stepWayPrm);
                //}
                context.SaveChanges();
                //return context.Way.FirstOrDefault(x => x.Way_ID.Equals(pRouteModel.RouteId)).Way_ID;
                return 1;
            }
        }

        public override int DeleteRoute(int pIdRoute)
        {
            using (var context = new tpksEntities())
            {
                try
                {
                    context.Way.Remove(context.Way.FirstOrDefault(x => x.Way_ID.Equals(pIdRoute)));
                    //context.Way_Prm.Remove(context.Way_Prm.FirstOrDefault(x => x.Way_ID.Equals(pIdRoute)));
                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    return 1;
                }
                //return pIdRoute;
                return 0;
            }
        }

        public override RouteModel GetRouteById(int pIdRoute)
        {
            try
            {
                using (var context = new tpksEntities())
                {
                    RouteModel tmpRoute = new RouteModel();
                    var route =
                        context.Way.FirstOrDefault(x => x.Way_ID.Equals(pIdRoute));
                    tmpRoute.RouteId = route.Way_ID;
                    tmpRoute.RouteName = route.Way_Name;
                    List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                    foreach (var item in route.Way_Prm)
                    {
                        Way_Prm wayPrm = new Way_Prm();
                        wayPrm.Cntr = item.Cntr;
                        wayPrm.Way_ID = item.Way_ID;
                        wayPrm.Prm_Name = item.Prm_Name;
                        wayPrm.Prm_Value = item.Prm_Value;
                        wayPrm.Field_Type = item.Field_Type;
                        wayParams.Add(wayPrm);
                    }
                    tmpRoute.WayParams = wayParams;
                    List<PointModel> points = new List<PointModel>();
                    foreach (var item in route.Step)
                    {
                        PointModel point = new PointModel();
                        Context.Step step = new Step();
                        step.Step_ID = item.Step_ID;
                        step.Step_Name = item.Step_Name;
                        step.Way_ID = item.Way_ID;
                        step.Step_Nmbr = item.Step_Nmbr;
                        point.Step = step;
                        List<Context.Step_Prm> Params = new List<Step_Prm>();
                        foreach (var item2 in item.Step_Prm.Where(x => x.Step_ID == item.Step_ID))
                        {
                            Step_Prm param = new Step_Prm();
                            param.Cntr = item2.Cntr;
                            param.Step_ID = item2.Step_ID;
                            param.Prm_Name = item2.Prm_Name;
                            param.Prm_Value = item2.Prm_Value;
                            param.Field_Type = item2.Field_Type;
                            Params.Add(param);
                        }
                        point.Params = Params;
                        List<Context.Step_Field> fields = new List<Step_Field>();
                        foreach (var item3 in item.Step_Field.Where(x => x.Step_ID == item.Step_ID))
                        {
                            Step_Field field = new Step_Field();
                            field.Cntr = item3.Cntr;
                            field.Step_ID = item3.Step_ID;
                            field.Field_Name = item3.Field_Name;
                            field.Field_Value = item3.Field_Value;
                            field.Field_Type = item3.Field_Type;
                            field.Visible_Flag = item3.Visible_Flag;
                            field.Editable_Falg = item3.Editable_Falg;
                            fields.Add(field);
                        }
                        point.Fields = fields;
                        points.Add(point);
                    }
                    tmpRoute.Points = points;
                    tmpRoute.RouteString = tmpRoute.Transformation();
                    return tmpRoute;
                }
            }
            catch (Exception ex)
            {
                RouteModel tmpRoute = new RouteModel();
                tmpRoute.RouteId = -2;
                return tmpRoute;
            }

        }

        public override List<RouteModel> GetFilteredUsersRoutes(RouteFilter_l pFilter, int id)
        {
            int OrderNum;
            if ((pFilter.OrderEnum == (RouteOrderEnum)1) && (pFilter.IsOrderDesc == true))
                OrderNum = 1;
            else
            {
                if ((pFilter.OrderEnum == (RouteOrderEnum)1) && (pFilter.IsOrderDesc == false))
                    OrderNum = 2;
                else
                {
                    OrderNum = 0;
                }
            }
            List<models.RouteModel> tmplist = new List<models.RouteModel>();
            try
            {
                using (var context = new tpksEntities())
                {
                    switch (OrderNum)
                    {
                        case 0:
                            {
                                //var route = context.Way.Include("Way_Prm");
                                var route=(from w in context.Way join d in context.Doc on w.Way_ID equals d.Way_ID where d.Current_User_ID==id select w).Distinct().ToList();
                                pFilter.SearchString = "";
                                foreach (var item in route)
                                {
                                    if ((item.Way_Name.IndexOf(pFilter.SearchString) > -1) || (pFilter.SearchString == ""))
                                    {
                                        RouteModel tmpRoute = new RouteModel();
                                        tmpRoute.RouteId = item.Way_ID;
                                        tmpRoute.RouteName = item.Way_Name;
                                        List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                                        foreach (var item1 in item.Way_Prm.Where(x => x.Way_ID == tmpRoute.RouteId))
                                        {
                                            Way_Prm wayPrm = new Way_Prm();
                                            wayPrm.Cntr = item1.Cntr;
                                            wayPrm.Way_ID = item1.Way_ID;
                                            wayPrm.Prm_Name = item1.Prm_Name;
                                            wayPrm.Prm_Value = item1.Prm_Value;
                                            wayPrm.Field_Type = item1.Field_Type;
                                            wayParams.Add(wayPrm);
                                        }
                                        tmpRoute.WayParams = wayParams;
                                        tmplist.Add(tmpRoute);
                                    }
                                }
                                if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                                else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                                else
                                    return tmplist;
                            }
                        case 1:
                            {
                                //var route = context.Way.OrderBy(x => x.Way_Name).Include("Way_Prm");
                                var route = (from w in context.Way join d in context.Doc on w.Way_ID equals d.Way_ID where d.Current_User_ID == id orderby w.Way_Name select w).Distinct().ToList();
                                foreach (var item in route)
                                {
                                    if ((item.Way_Name.IndexOf(pFilter.SearchString) > -1) || (pFilter.SearchString == ""))
                                    {
                                        RouteModel tmpRoute = new RouteModel();
                                        tmpRoute.RouteId = item.Way_ID;
                                        tmpRoute.RouteName = item.Way_Name;
                                        List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                                        foreach (var item1 in item.Way_Prm.Where(x => x.Way_ID == tmpRoute.RouteId))
                                        {
                                            Way_Prm wayPrm = new Way_Prm();
                                            wayPrm.Cntr = item1.Cntr;
                                            wayPrm.Way_ID = item1.Way_ID;
                                            wayPrm.Prm_Name = item1.Prm_Name;
                                            wayPrm.Prm_Value = item1.Prm_Value;
                                            wayPrm.Field_Type = item1.Field_Type;
                                            wayParams.Add(wayPrm);
                                        }
                                        tmpRoute.WayParams = wayParams;
                                        tmplist.Add(tmpRoute);
                                    }
                                }
                                if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                                else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                                else
                                    return tmplist;
                            }
                        case 2:
                            {
                                //var route = context.Way.OrderByDescending(x => x.Way_Name).Include("Way_Prm");
                                var route = (from w in context.Way join d in context.Doc on w.Way_ID equals d.Way_ID where d.Current_User_ID == id orderby w.Way_Name descending select w).Distinct().ToList();
                                foreach (var item in route)
                                {
                                    if ((item.Way_Name.IndexOf(pFilter.SearchString) > -1) || (pFilter.SearchString == ""))
                                    {
                                        RouteModel tmpRoute = new RouteModel();
                                        tmpRoute.RouteId = item.Way_ID;
                                        tmpRoute.RouteName = item.Way_Name;
                                        List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                                        foreach (var item1 in item.Way_Prm.Where(x => x.Way_ID == tmpRoute.RouteId))
                                        {
                                            Way_Prm wayPrm = new Way_Prm();
                                            wayPrm.Cntr = item1.Cntr;
                                            wayPrm.Way_ID = item1.Way_ID;
                                            wayPrm.Prm_Name = item1.Prm_Name;
                                            wayPrm.Prm_Value = item1.Prm_Value;
                                            wayPrm.Field_Type = item1.Field_Type;
                                            wayParams.Add(wayPrm);
                                        }
                                        tmpRoute.WayParams = wayParams;
                                        tmplist.Add(tmpRoute);
                                    }
                                }
                                if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                                else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                                else
                                    return tmplist;
                            }
                        default:
                            {
                                return tmplist;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.ToString());

                return null;
            }
        }

        public override List<RouteModel> GetFilteredRoutes(RouteFilter_l pFilter)
        {
            int OrderNum;
            if ((pFilter.OrderEnum == (RouteOrderEnum)1) && (pFilter.IsOrderDesc == true))
                OrderNum = 1;
            else
            {
                if ((pFilter.OrderEnum == (RouteOrderEnum)1) && (pFilter.IsOrderDesc == false))
                    OrderNum = 2;
                else
                {
                    OrderNum = 0;
                }
            }
            List<models.RouteModel> tmplist = new List<models.RouteModel>();
            try
            {
                using (var context = new tpksEntities())
                {
                    switch (OrderNum)
                    {
                        case 0:
                            {
                                var route = context.Way.Include("Way_Prm");
                                pFilter.SearchString = "";
                                foreach (var item in route)
                                {
                                    if ((item.Way_Name.IndexOf(pFilter.SearchString) > -1) || (pFilter.SearchString == ""))
                                    {
                                        RouteModel tmpRoute = new RouteModel();
                                        tmpRoute.RouteId = item.Way_ID;
                                        tmpRoute.RouteName = item.Way_Name;
                                        List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                                        foreach (var item1 in item.Way_Prm.Where(x => x.Way_ID == tmpRoute.RouteId))
                                        {
                                            Way_Prm wayPrm = new Way_Prm();
                                            wayPrm.Cntr = item1.Cntr;
                                            wayPrm.Way_ID = item1.Way_ID;
                                            wayPrm.Prm_Name = item1.Prm_Name;
                                            wayPrm.Prm_Value = item1.Prm_Value;
                                            wayPrm.Field_Type = item1.Field_Type;
                                            wayParams.Add(wayPrm);
                                        }
                                        tmpRoute.WayParams = wayParams;
                                        tmplist.Add(tmpRoute);
                                    }
                                }
                                if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                                else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                                else
                                    return tmplist;
                            }
                        case 1:
                            {
                                var route =context.Way.OrderBy(x=>x.Way_Name).Include("Way_Prm");
                                foreach (var item in route)
                                {
                                    if ((item.Way_Name.IndexOf(pFilter.SearchString) > -1)|| (pFilter.SearchString==""))
                                    {
                                        RouteModel tmpRoute = new RouteModel();
                                        tmpRoute.RouteId = item.Way_ID;
                                        tmpRoute.RouteName = item.Way_Name;
                                        List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                                        foreach (var item1 in item.Way_Prm.Where(x=>x.Way_ID==tmpRoute.RouteId))
                                        {
                                            Way_Prm wayPrm = new Way_Prm();
                                            wayPrm.Cntr = item1.Cntr;
                                            wayPrm.Way_ID = item1.Way_ID;
                                            wayPrm.Prm_Name = item1.Prm_Name;
                                            wayPrm.Prm_Value = item1.Prm_Value;
                                            wayPrm.Field_Type = item1.Field_Type;
                                            wayParams.Add(wayPrm);
                                        }
                                        tmpRoute.WayParams = wayParams;
                                        tmplist.Add(tmpRoute);
                                    }
                                }
                                if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                                else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                                else
                                    return tmplist;
                            }
                        case 2:
                            {
                                var route = context.Way.OrderByDescending(x => x.Way_Name).Include("Way_Prm");
                                
                                foreach (var item in route)
                                {
                                    if ((item.Way_Name.IndexOf(pFilter.SearchString) > -1) || (pFilter.SearchString == ""))
                                    {
                                        RouteModel tmpRoute = new RouteModel();
                                        tmpRoute.RouteId = item.Way_ID;
                                        tmpRoute.RouteName = item.Way_Name;
                                        List<Context.Way_Prm> wayParams = new List<Way_Prm>();
                                        foreach (var item1 in item.Way_Prm.Where(x => x.Way_ID == tmpRoute.RouteId))
                                        {
                                            Way_Prm wayPrm = new Way_Prm();
                                            wayPrm.Cntr = item1.Cntr;
                                            wayPrm.Way_ID = item1.Way_ID;
                                            wayPrm.Prm_Name = item1.Prm_Name;
                                            wayPrm.Prm_Value = item1.Prm_Value;
                                            wayPrm.Field_Type = item1.Field_Type;
                                            wayParams.Add(wayPrm);
                                        }
                                        tmpRoute.WayParams = wayParams;
                                        tmplist.Add(tmpRoute);
                                    }
                                }
                                if (pFilter.StartIndex != null & pFilter.Count != null & (pFilter.Count + pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, (int)pFilter.Count);
                                else if (pFilter.StartIndex != null & (pFilter.StartIndex <= tmplist.Count))
                                    return tmplist.GetRange((int)pFilter.StartIndex, tmplist.Count - (int)pFilter.StartIndex);
                                else
                                    return tmplist;
                            }
                        default:
                            {
                                return tmplist;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write(ex.ToString());

                return null;
            }
            
        }

        #endregion


        

        
    }
}
